<?php

class SqlEngine {

    public static function getUser($filters = array()) {
        $pdo_params = array();

        $query = "SELECT *
                    FROM user
                   WHERE 1 = 1";

        if (!empty($filters['id'])) {
            $query .= " AND user_id = :id";
            $pdo_params['id'] = $filters['id'];
        }

        if (isset($filters['password'])) {
            $query .= " AND password = md5(:password)";
            $pdo_params['password'] = $filters['password'];
        }
        
        if (isset($filters['username'])) {
            $query .= " AND username = :username";
            $pdo_params['username'] = $filters['username'];
        }

        if (isset($filters['user_type'])) {
            $query .= " AND user_type = :user_type";
            $pdo_params['user_type'] = $filters['user_type'];
        }

        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getRestaurants($filters = array()) {
        $pdo_params = array();

        $query = "SELECT * 
                    FROM restaurant
                   WHERE 1 = 1";

        if (!empty($filters['name'])) {
            $query .= " AND name = :name";
            $pdo_params['name'] = $filters['name'];
        }
        
        if (!empty($filters['restaurant_id'])) {
            $query .= " AND restaurant_id = :restaurant_id";
            $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        }

        if (!empty($filters['name_like'])) {
            $query .= " AND name LIKE :name_like";
            $pdo_params['name_like'] = '%' . $filters['name_like'] . '%';
        }

        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getUserRestaurants($filters = array()) {
        $pdo_params = array();

        $query = "SELECT *
                    FROM restaurant
               LEFT JOIN user_restaurant ON user_restaurant.restaurant_id = restaurant.restaurant_id
                   WHERE 1 = 1";

        if (!empty($filters['name'])) {
            $query .= " AND name = :name";
            $pdo_params['name'] = $filters['name'];
        }

        if (!empty($filters['user_id'])) {
            $query .= " AND user_restaurant.user_id = :user_id";
            $pdo_params['user_id'] = $filters['user_id'];
        }

        if (!empty($filters['name_like'])) {
            $query .= " AND name LIKE :name_like";
            $pdo_params['name_like'] = '%' . $filters['name_like'] . '%';
        }

        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getConsoleReservations($filters = array()) {
        $pdo_params = array();

        $query = "SELECT reservation.*,
                         (SELECT MAX(created_at) FROM reservation) last_created_at
                    FROM reservation
               LEFT JOIN console_log ON (
                                console_log.reservation_id = reservation.reservation_id
                            AND console_log.restaurant_id = reservation.restaurant_id
                            AND console_log.user_id = :user_id)
                   WHERE console_log.reservation_id IS NULL
                     AND reservation.restaurant_id = :restaurant_id
                     AND reservation.date >= date(NOW())";

        $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        $pdo_params['user_id'] = $filters['user_id'];
        
        if (!empty($filters['after_created_at'])) {
            $query .= " AND reservation.created_at > :after_created_at";
            $pdo_params['after_created_at'] = $filters['after_created_at'];
        }

        $query .= " ORDER BY reservation.created_at ASC";
//var_dump($filters);
//var_dump($query);
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getReservations($filters = array()) {
        $pdo_params = array();
        
        $query = "SELECT *, (SELECT MAX(created_at) FROM reservation) last_created_at
                    FROM reservation
                   WHERE 1 = 1";

        if (!empty($filters['not_deleted'])) {
            $query .= " AND deleted_at IS NULL";
        }

        if (!empty($filters['today_and_after'])) {
            $query .= " AND date >= date(NOW())";
        }
        
        if (!empty($filters['reservation_id'])) {
            $query .= " AND reservation_id = :reservation_id";
            $pdo_params['reservation_id'] = $filters['reservation_id'];
        }

        if (!empty($filters['restaurant_id'])) {
            $query .= " AND restaurant_id = :restaurant_id";
            $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        }
        
        if (!empty($filters['token'])) {
            $query .= " AND token = :token";
            $pdo_params['token'] = $filters['token'];
        }

        if (!empty($filters['time'])) {
            $query .= " AND time = :time";
            $pdo_params['time'] = $filters['time'];
        }
        
        if (!empty($filters['time_between'])) {
            $query .= " AND time >= :time_start AND time <= :time_end";
            $pdo_params['time_start'] = $filters['time_between'][0];
            $pdo_params['time_end'] = $filters['time_between'][1];
        }

        if (!empty($filters['date'])) {
            $query .= " AND date = :date";
            $pdo_params['date'] = $filters['date'];
        }

        if (!empty($filters['after_created_at'])) {
            $query .= " AND created_at > :after_created_at";
            $pdo_params['after_created_at'] = $filters['after_created_at'];
        }

        if (!empty($filters['order']))
            $query .= " ORDER BY " . $filters['order'][0] . ' ' . $filters['order'][1];
        else
            $query .= " ORDER BY time";
//echo $query;
//var_dump($filters);
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getDayIntervalsInfo($filters = array()) {
        $pdo_params = array();
        
        $query = "SELECT *
                    FROM settings_time_schedule
                   WHERE day = :day
                     AND restaurant_id = :restaurant_id";
        
        if (!empty($filters['time'])) {
            $query .= " AND :time1 >= time_start AND :time2 <= time_end";
            $pdo_params['time1'] = $filters['time'];
            $pdo_params['time2'] = $filters['time'];
        }
        
        $query .= " ORDER BY str_to_date(time_start,'%k:%i')";
        
        $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        $pdo_params['day'] = $filters['day'];
//        echo $query;
//        var_dump($filters);
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getReservationTimes($filters = array()) {
        $query = "SELECT time 
                    FROM reservation
                   WHERE time >= :time_start
                     AND time < :time_end
                     AND date = :date
                     AND restaurant_id = :restaurant_id
                     AND deleted_at IS NULL
                   GROUP BY time";

        return SqlEngineCore::get()->executeSql($query, $filters);
    }

    public static function getSpecialDates($filters = array()) {
        $pdo_params = array();
        
        $query = "SELECT *
                    FROM settings_special_date
                   WHERE date >= DATE(NOW())";

        if (!empty($filters['restaurant_id'])) {
            $query .= " AND restaurant_id = :restaurant_id";
            $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        }

        $query .= " ORDER BY date ASC";

        if (!empty($filters['count'])) {
            $query .= " LIMIT " . (int) $filters['count'];
        }

        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getSettings($filters = array()) {
        $query = "SELECT *
                    FROM settings
                   WHERE restaurant_id = :restaurant_id";
        
        return SqlEngineCore::get()->executeSql($query, $filters, true);
    }

    public static function getTemplates($filters = array()) {
        $pdo_params = array();
        
        $query = "SELECT *
                    FROM template
                   WHERE 1 = 1";

        if (!empty($filters['category_id'])) {
            $query .= " AND template_category_id = :category_id";
            $pdo_params['category_id'] = $filters['category_id'];
        }
        
        if (!empty($filters['type_id'])) {
            $query .= " AND template_type_id = :type_id";
            $pdo_params['type_id'] = $filters['type_id'];
        }
        
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getTemplateTypes($filters = array()) {
        $pdo_params = array();
        
        $query = "SELECT *
                    FROM template_type
                   WHERE 1 = 1";

        if (!empty($filters['name'])) {
            $query .= " AND name = :name";
            $pdo_params['name'] = $filters['name'];
        }

        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getTemplateCategory($filters = array()) {
        $pdo_params = array();

        $query = "SELECT *
                    FROM template_category
                   WHERE 1 = 1";

        if (!empty($filters['name'])) {
            $query .= " AND name = :name";
            $pdo_params['name'] = $filters['name'];
        }

        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getMenus($filters = array()) {
        $pdo_params = array();
        
        $query = "SELECT *
                    FROM menu
                   WHERE 1 = 1";

        if (!empty($filters['restaurant_id'])) {
            $query .= " AND restaurant_id = :restaurant_id";
            $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        }
        
        if (!empty($filters['menu_id'])) {
            $query .= " AND menu_id = :menu_id";
            $pdo_params['menu_id'] = $filters['menu_id'];
        }
        
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }

    public static function getBlockTimes($filters = array()) {
        $pdo_params = array();

        $query = "SELECT *
                    FROM settings_dead_time
                   WHERE 1 = 1";

        if (!empty($filters['restaurant_id'])) {
            $query .= " AND restaurant_id = :restaurant_id";
            $pdo_params['restaurant_id'] = $filters['restaurant_id'];
        }
        
        if (!empty($filters['date'])) {
            $query .= " AND :date1 >= date_start AND :date2 <= date_end";
            $pdo_params['date1'] = $filters['date'];
            $pdo_params['date2'] = $filters['date'];
        }
        
        if (!empty($filters['time'])) {
            $query .= " AND :time1 >= time_start AND :time2 <= time_end";
            $pdo_params['time1'] = $filters['time'];
            $pdo_params['time2'] = $filters['time'];
        }
        
        $query .= " ORDER BY date_start ASC, time_start ASC";
//echo $query;
//var_dump($filters);
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }
    
    public static function getReservationLogs($reservationId = null) {
        $pdo_params = array();
        
        $query = "SELECT *
                    FROM reservation_log
                   WHERE 1 = 1";

        if (null != $reservationId) {
            $query .= " AND reservation_id = :reservation_id";
            $pdo_params['reservation_id'] = $reservationId;
        }
        
        return SqlEngineCore::get()->executeSql($query, $pdo_params);
    }
}
